﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for StaffDA
/// </summary>
public class StaffDA
{
    private DataAccess da;

	public StaffDA()
	{
		//
		// TODO: Add constructor logic here
		//
        da = new DataAccess();
	}
    public DataSet staffLogin(string username, string pass)
    {
        string query = "select * from Staff where userName=@username and pass=@pass";
        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@username", SqlDbType.VarChar);
        sqlParameters[0].Value = Convert.ToString(username);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.VarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        DataSet ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public bool insertStudent(Student s)
    {
        int studentId = s.Id;
        string userName = s.Username;
        string pass = s.Password;
        string fullName = s.Fullname;
        string address = s.Address;
        string email = s.Email;
        string phoneNo = s.PhoneNumber;
        string major = s.Major;
        string _class = s._class1;

        string query = "insert into Student values(@userName,@pass,@fullName,@address,@email,@phoneNo,@major,@class)";

        SqlParameter[] sqlParameters = new SqlParameter[8];
        sqlParameters[0] = new SqlParameter("@userName", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(userName);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        sqlParameters[2] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(fullName);
        sqlParameters[3] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(address);
        sqlParameters[4] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(email);
        sqlParameters[5] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(phoneNo);
        sqlParameters[6] = new SqlParameter("@major", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(major);
        sqlParameters[7] = new SqlParameter("@class", SqlDbType.NVarChar);
        sqlParameters[7].Value = Convert.ToString(_class);

        return da.executeInsertQuery(query, sqlParameters);
    }

    public bool updateStudent(Student s)
    {
        int studentId = s.Id;
        string userName = s.Username;
        string pass = s.Password;
        string fullName = s.Fullname;
        string address = s.Address;
        string email = s.Email;
        string phoneNo = s.PhoneNumber;
        string major = s.Major;
        string _class = s._class1;

        string query = "update Student set pass=@pass,fullName=@fullName,address=@address,email=@email,phoneNo=@phoneNo,major=@major,class=@class where studentId=@studentId";

        SqlParameter[] sqlParameters = new SqlParameter[8];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(studentId);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        sqlParameters[2] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(fullName);
        sqlParameters[3] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(address);
        sqlParameters[4] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(email);
        sqlParameters[5] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(phoneNo);
        sqlParameters[6] = new SqlParameter("@major", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(major);
        sqlParameters[7] = new SqlParameter("@class", SqlDbType.NVarChar);
        sqlParameters[7].Value = Convert.ToString(_class);

        return da.executeUpdateQuery(query, sqlParameters);
    }

    public bool delStudent(Student s)
    {
        int studentId = s.Id;

        string query = "delete Student where studentId=@studentId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(studentId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public DataSet getAllStudent()
    {
        DataSet ds = new DataSet();
        string query = "select * from Student order by userName asc";

        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);

        return ds;
    }

    public DataSet searchStudent(string name)
    {
        DataSet ds = new DataSet();
        string query = "select * from Student where userName='" + name + "' order by userName asc";
        SqlParameter[] sqlParameters = new SqlParameter[0];

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet searchStudentByName(string name)
    {
        DataSet ds = new DataSet();
        string query = "select * from Student where fullName like '%" + name + "%' order by studentId asc";
        SqlParameter[] sqlParameters = new SqlParameter[0];

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public bool delDesign(Design d)
    {
        int designId = d.Id;

        string query = "delete Design where designId=@designId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@designId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(designId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public DataSet getAllDesign()
    {
        DataSet ds = new DataSet();
        string query = "select d.designId,d.name,d.postDate,d.description,d.quotation,st.userName,c.name from Student st inner join Design d on st.studentId=d.studentId inner join Competition c on d.comId=c.comId order by d.name asc";
        SqlParameter[] sqlParameters = new SqlParameter[0];

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet getAllDesignOnCompetitition(Competition c)
    {
        int comId = c.ComId;
        DataSet ds = new DataSet();
        string query = "select d.designId,d.name,d.postDate,d.description,d.quotation,st.userName from Student st inner join Design d on st.studentId=d.studentId where d.comId=@comId order by d.name asc";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(comId);

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet searchDesign(Design d)
    {
        int designId = d.Id;
        DataSet ds = new DataSet();
        string query = "select d.designId,d.name,d.postDate,d.description,d.quotation,st.userName,c.name,d.designImage from Student st inner join Design d on st.studentId=d.studentId inner join Competition c on d.comId=c.comId where d.designId=@designId order by d.name asc";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@designId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(designId);

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet getDesignsByStudent(Student s)
    {
        DataSet ds = new DataSet();
        int studentId = s.Id;

        string query = "select d.designId,d.name,d.postDate,d.description,d.quotation,st.userName,c.name from Student st inner join Design d on st.studentId=d.studentId inner join Competition c on d.comId=c.comId where studentId=@studentId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(studentId);

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public bool insertMark(Mark m)
    {
        int staffId = m.Staff.Id;
        int designId = m.Design.Id;
        double mark = m.Mark1;

        string query = "insert into Mark values(@staffId,@designId,@mark)";

        SqlParameter[] sqlParameters = new SqlParameter[3];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);
        sqlParameters[1] = new SqlParameter("@designId", SqlDbType.BigInt);
        sqlParameters[1].Value = Convert.ToString(designId);
        sqlParameters[2] = new SqlParameter("@mark", SqlDbType.Float);
        sqlParameters[2].Value = Convert.ToString(mark);

        return da.executeInsertQuery(query, sqlParameters);
    }

    public bool updateMark(Mark m)
    {
        int markId = m.Id;
        double mark = m.Mark1;

        string query = "update Mark set mark=@mark where markId=@markId";

        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@markId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(markId);
        sqlParameters[1] = new SqlParameter("@mark", SqlDbType.Float);
        sqlParameters[1].Value = Convert.ToString(mark);

        return da.executeUpdateQuery(query, sqlParameters);
    }

    //reset Mark=0 (truong hop bai Design pham quy...)
    public bool resetMark(Mark m)
    {
        int markId = m.Id;
        float mark = 0;

        string query = "update Mark set mark=@mark where markId=@markId";

        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@markId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(markId);
        sqlParameters[1] = new SqlParameter("@mark", SqlDbType.Float);
        sqlParameters[1].Value = Convert.ToString(mark);

        return da.executeUpdateQuery(query, sqlParameters);
    }

    public bool delMark(Mark m)
    {
        int markId = m.Id;

        string query = "delete Mark where markId=@markId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@markId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(markId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public Mark getMarkByDesign(Design d)
    {
        int designId = d.Id;
        string query = "select m.markId,s.userName,d.name from Staff s inner join Mark m on s.staffId=m.staffId inner join Design d on m.designId=d.designId where designId=@designId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@designId", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(designId);

        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);

        Mark m = new Mark();
        DataRow dr = ds.Tables[0].Rows[0];
        m.Id = Convert.ToInt32(dr[0].ToString());
        Staff s=new Staff();
        s.Id=Convert.ToInt32(dr[1].ToString());
        m.Staff = s;
        Design d1 = new Design();
        d1.Id = Convert.ToInt32(dr[2].ToString());
        m.Design = d1;
        m.Mark1 = Convert.ToDouble(dr[3].ToString());
        return m;
    }

    public DataSet getMarkByStaff(Staff s)
    {
        int staffId = s.Id;

        string query = "select m.markId,s.userName,d.name from Staff s inner join Mark m on s.staffId=m.staffId inner join Design d on m.designId=d.designId where staffId=@staffId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);

        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet getCompetitionAccess(int id)
    {
        string query = "select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation from Competition c inner join StaffCompetition sc on c.comId=sc.comId where sc.staffId=@id order by c.comId asc";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@id", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(id);

        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
}